﻿



create proc Products.SaveProduct (
	@ProductID			uniqueidentifier = null output,
	@ProductGroupID		uniqueidentifier,	
	@Name				nvarchar(255),
	@Description		nvarchar(400),
	@Length			decimal(11,2),
	@Width			decimal(11,2),
	@Height			decimal(11,2),
	@Listenpreis		decimal(11,2),
	@Unit				char(5),
	@Rabatt1			decimal(11,2),
	@Rabatt2			decimal(11,2),
	@Skonto			decimal(11,2),
	@Db				decimal(11,2) = 0,
	@Quantity			decimal(11,2),
	@ProducerID			uniqueidentifier,
	@VendorID			uniqueidentifier,
	@GiltAb			datetime2,
	@Status			smallint,
	@ErrorMessage		nvarchar(400) output
)
as
begin

	if @ProductID is null
	begin
		select @ProductID = NEWID();
	end
	
	if @ProductGroupID is null
	begin
		set @ErrorMessage = '@ProductGroupID ist null';
		goto EndWithError;
	end
	if IsNull(@Name,'') = ''
	begin
		set @ErrorMessage = 'Name ist null';
		goto EndWithError;
	end
	if IsNull(@Description,'') = ''
	begin
		set @ErrorMessage = 'Description ist null';
		goto EndWithError;
	end
	if @Length is null
	begin
		set @ErrorMessage = 'Length ist null';
		goto EndWithError;
	end
	if @Width is null
	begin
		set @ErrorMessage = 'Width ist null';
		goto EndWithError;
	end
	if @Height is null
	begin
		set @ErrorMessage = 'Height ist null';
		goto EndWithError;
	end
	if @Listenpreis is null
	begin
		set @ErrorMessage = 'Listenpreis ist null';
		goto EndWithError;
	end
	if IsNull(@Unit,'') = ''
	begin
		set @ErrorMessage = 'Unit is null or empty';
		goto EndWithError;
	end
	if @Rabatt1 is null
	begin
		set @ErrorMessage = 'Rabatt1 ist null';
		goto EndWithError;
	end
	if @Rabatt2 is null
	begin
		set @ErrorMessage = 'Rabatt2 ist null';
		goto EndWithError;
	end
	if @Skonto is null
	begin
		set @ErrorMessage = 'Skonto ist null';
		goto EndWithError;
	end
	if @Db is null
	begin
		set @ErrorMessage = 'Db ist null';
		goto EndWithError;
	end
	if @Quantity is null
	begin
		set @ErrorMessage = 'Quantity ist null';
		goto EndWithError;
	end
	if @ProducerID is null
	begin
		set @ErrorMessage = 'ProducerID ist null';
		goto EndWithError;
	end
	if @Status is null
	begin
		set @ErrorMessage = 'Status ist null';
		goto EndWithError;
	end
	if @VendorID is null
	begin
		set @ErrorMessage = 'VendorID ist null';
		goto EndWithError;
	end
	if @GiltAb is null
	begin
		set @ErrorMessage = 'GiltAb ist null';
		goto EndWithError;
	end
	
	begin tran;
	
	
	
	with src 
	as
	(
		Select	@ProductID 'ProductID',
				@ProductGroupID 'ProductGroupID',
				@Name 'Name',
				@Description 'Description',
				@Length 'Length',
				@Width 'Width',
				@Height 'Height',
				@Listenpreis 'Listenpreis',
				@Unit 'Unit',
				@Rabatt1 'Rabatt1',
				@Rabatt2 'Rabatt2',
				@Skonto 'Skonto',
				@Db 'Db',
				@Quantity 'Quantity',
				@ProducerID 'ProducerID',
				@VendorID 'VendorID',
				@GiltAb 'GiltAb',
				@Status 'Status'
	)
	
	merge Products.datProduct as target
	using src
		on (target.ProductID = src.ProductID)
	when matched then 
		update SET	
				ProductID		= src.ProductID,
				ProductGroupID	= src.ProductGroupID,
				Name			= src.Name,
				[Description]	= src.[Description],
				[Length]		= src.[Length],
				Width			= src.Width,
				Height		= src.Height,
				Listenpreis		= src.Listenpreis,
				Unit			= src.Unit,
				Rabatt1		= src.Rabatt1,
				Rabatt2		= src.Rabatt2,
				Skonto		= src.Skonto,
				Db			= src.Db,
				Quantity		= src.Quantity,
				ProducerID		= src.ProducerID,
				VendorID		= src.VendorID,
				GiltAb		= src.GiltAb,
				[Status]		= src.[Status]
				
	when not matched by target then
		insert (
			ProductID,
			ProductGroupID,
			Name,
			[Description],
			[Length],
			Width,
			Height,
			Listenpreis,
			Unit,
			Rabatt1,
			Rabatt2,
			Skonto,
			Db,
			Quantity,
			ProducerID,
			VendorID,
			GiltAb,
			[Status]
		)
		values (
			src.ProductID,
			src.ProductGroupID,
			src.Name,
			src.[Description],
			src.[Length],
			src.Width,
			src.Height,
			src.Listenpreis,
			src.Unit,
			src.Rabatt1,
			src.Rabatt2,
			src.Skonto,
			src.Db,
			src.Quantity,
			src.ProducerID,
			src.VendorID,
			src.GiltAb,
			src.[Status]
		);
		
	
	commit;
	return 0;
	
EndWithError:
	RAISERROR ( @ErrorMessage, 16, 1);
	return 1;	

end
GO


